Project 1 - US Accident Injury Dataset EDA

---

Introduction

todo … We will investigate the “US Accident Injury Dataset” published by US Department of Labour. The entire dataset spans across 15 years (\(2000\) to \(2015\)) and has a total of \(202814\) records.

---

Import

The dataset contains a dictionary. Importantly, the dictionary states:

• “May contain null values”
• “May be ‘?’ if invalid code”
• “May be ‘No Value Found’ if invalid…”

These terms above were validated by a visual scan of the data using bash commands. Cells that “may contain null values” were discovered to be represented as empty strings in the dataset. Upon import, we will set all these cells to NA and reference the data dictionary to infer its meaning.

note: The Data.gov site no longer gives access to this data giving a \(404\) error. The data was downloaded from LMS and stored in the directory of this RMarkdown file to resolve this issue.

df <- read.csv('us_data.csv', na = c('?', '', 'NO VALUE FOUND'), as.is = FALSE, header = TRUE)

Confirm Dimensions:

[1] 202814     57

head(df)

---

Data Wrangling

Let us look at the overall structure of the data.

str(df)

'data.frame':   202814 obs. of  57 variables:
 $ MINE_ID            : int  100003 100003 100008 100011 100011 100011 100011 100011 100016 100021 ...
 $ CONTROLLER_ID      : Factor w/ 5147 levels "100065","100167",..: 359 359 4654 4006 4006 4006 4006 4006 2311 2248 ...
 $ CONTROLLER_NAME    : Factor w/ 5145 levels "(Allen) L  Scott",..: 3009 3009 52 2043 2043 2043 2043 2043 4448 2981 ...
 $ OPERATOR_ID        : Factor w/ 6645 levels "100045","100065",..: 3624 3624 5015 4455 4455 4455 1888 1888 3451 4195 ...
 $ OPERATOR_NAME      : Factor w/ 8187 levels " Bill Smith S&G",..: 4354 4354 1566 3602 3602 3602 3590 3590 6930 7808 ...
 $ CONTRACTOR_ID      : Factor w/ 3804 levels "1AI","1AR","1AW",..: NA NA NA NA NA NA NA NA NA NA ...
 $ DOCUMENT_NO        : num  2.2e+11 2.2e+11 2.2e+11 2.2e+11 2.2e+11 ...
 $ SUBUNIT_CD         : int  3 30 30 30 30 30 30 30 3 3 ...
 $ SUBUNIT            : Factor w/ 10 levels "AUGER","CULM BANK/REFUSE PILE",..: 8 5 5 5 5 5 5 5 8 8 ...
 $ ACCIDENT_DT        : Factor w/ 5608 levels "1/01/2000","1/01/2001",..: 968 5246 4603 3380 2586 3893 5387 2304 1968 23 ...
 $ CAL_YR             : int  2012 2007 2009 2000 2005 2006 2008 2012 2000 2006 ...
 $ CAL_QTR            : int  1 1 3 2 1 1 4 2 3 1 ...
 $ FISCAL_YR          : int  2012 2007 2009 2000 2005 2006 2009 2012 2000 2006 ...
 $ FISCAL_QTR         : int  2 2 4 3 2 2 1 3 4 2 ...
 $ ACCIDENT_TIME      : int  945 1105 1000 1100 1430 1130 430 930 730 230 ...
 $ DEGREE_INJURY_CD   : int  5 6 3 5 5 5 3 5 5 4 ...
 $ DEGREE_INJURY      : Factor w/ 11 levels "ACCIDENT ONLY",..: 4 9 3 4 4 4 3 4 4 5 ...
 $ FIPS_STATE_CD      : int  1 1 1 1 1 1 1 1 1 1 ...
 $ UG_LOCATION_CD     : int  NA NA NA NA NA NA NA NA NA NA ...
 $ UG_LOCATION        : Factor w/ 9 levels "FACE","INBY PERMANENT SUPPORT",..: NA NA NA NA NA NA NA NA NA NA ...
 $ UG_MINING_METHOD_CD: int  NA NA NA NA NA NA NA NA NA NA ...
 $ UG_MINING_METHOD   : Factor w/ 7 levels "Caving","Continuous Mining",..: NA NA NA NA NA NA NA NA NA NA ...
 $ MINING_EQUIP_CD    : int  24 28 NA NA NA NA 28 24 NA 44 ...
 $ MINING_EQUIP       : Factor w/ 69 levels "Aerial tram, Tramway",..: 24 28 NA NA NA NA 28 24 NA 46 ...
 $ EQUIP_MFR_CD       : int  119 121 NA NA NA NA 121 119 NA 18 ...
 $ EQUIP_MFR_NAME     : Factor w/ 189 levels "A. L. Lee","Abex",..: 139 140 NA NA NA NA 140 139 NA 30 ...
 $ EQUIP_MODEL_NO     : Factor w/ 14584 levels "-","--","---",..: 2123 NA NA NA NA NA NA 12926 NA NA ...
 $ SHIFT_BEGIN_TIME   : int  600 700 600 700 700 700 2300 700 700 1800 ...
 $ CLASSIFICATION_CD  : int  12 10 18 9 9 21 10 18 9 9 ...
 $ CLASSIFICATION     : Factor w/ 28 levels "ALL OTHER OCCUPATIONAL ILLNESSES",..: 24 14 26 13 13 22 14 26 13 13 ...
 $ ACCIDENT_TYPE_CD   : int  21 8 30 27 38 26 29 30 30 30 ...
 $ ACCIDENT_TYPE      : Factor w/ 44 levels "ABSRTN RAD CAUST TXC & NOX SBS",..: 6 43 32 31 1 3 34 32 32 32 ...
 $ NO_INJURIES        : int  1 1 1 1 1 1 1 1 1 1 ...
 $ TOT_EXPER          : num  4.35 0.02 10 NA 0.87 ...
 $ MINE_EXPER         : num  4.35 0.02 2.15 0.23 0.87 ...
 $ JOB_EXPER          : num  0.67 0.02 2.15 0.23 0.38 ...
 $ OCCUPATION_CD      : int  374 374 374 374 382 304 374 368 316 304 ...
 $ OCCUPATION         : Factor w/ 188 levels "Apprentice, Trainee",..: 178 178 178 178 70 103 178 26 92 103 ...
 $ ACTIVITY_CD        : int  28 30 13 28 96 39 30 23 28 39 ...
 $ ACTIVITY           : Factor w/ 98 levels "ACCIDENT RECOVERY - EQP/WORKERS",..: 30 26 14 30 96 38 26 23 30 38 ...
 $ INJURY_SOURCE_CD   : int  76 46 117 4 21 2 46 76 3 86 ...
 $ INJURY_SOURCE      : Factor w/ 126 levels "ABRAS STONE,WHEEL GRINDER",..: 115 6 51 8 2 14 6 115 15 72 ...
 $ NATURE_INJURY_CD   : int  160 180 330 330 130 330 330 330 400 260 ...
 $ NATURE_INJURY      : Factor w/ 37 levels "AMPUTATION OR ENUCLEATION",..: 8 10 34 34 4 34 34 34 37 22 ...
 $ INJ_BODY_PART_CD   : int  700 100 520 420 330 420 450 420 420 440 ...
 $ INJ_BODY_PART      : Factor w/ 46 levels "ABDOMEN/INTERNAL ORGANS",..: 32 22 2 5 20 5 36 5 5 23 ...
 $ SCHEDULE_CHARGE    : int  0 0 0 NA 0 0 0 0 NA 0 ...
 $ DAYS_RESTRICT      : int  8 0 0 5 5 3 0 21 10 19 ...
 $ DAYS_LOST          : int  0 0 9 NA 0 0 0 0 NA 13 ...
 $ TRANS_TERM         : Factor w/ 2 levels "N","Y": 1 1 1 1 1 1 NA 1 1 1 ...
 $ RETURN_TO_WORK_DT  : Factor w/ 5607 levels "01/13/2000","01/13/2001",..: 785 2688 1737 4886 4127 4559 NA 1830 3495 683 ...
 $ IMMED_NOTIFY_CD    : Factor w/ 14 levels "? ","1","10",..: 1 1 1 6 1 1 1 1 6 1 ...
 $ IMMED_NOTIFY       : Factor w/ 13 levels "DEATH","ENTRAPMENT",..: NA NA NA 9 NA NA NA NA 9 NA ...
 $ INVEST_BEGIN_DT    : Factor w/ 5420 levels "01/13/2000","01/13/2001",..: NA NA NA NA 147 NA NA NA NA NA ...
 $ NARRATIVE          : Factor w/ 201714 levels "-----Original Complaint on 11-26-08---- EMPLOYEE WAS ABOUT TO ENTER THE TROMMEL ON HIS HANDS AND KNEES. HE DIDN"| __truncated__,..: 87667 126665 87957 127216 32164 55082 198500 125138 58250 107965 ...
 $ CLOSED_DOC_NO      : num  NA NA 3.2e+11 3.2e+11 NA ...
 $ COAL_METAL_IND     : Factor w/ 2 levels "C","M": 2 2 2 2 2 2 2 2 2 2 ...

Drop less important variables

Bajesus, we have a lot of columns! We should aim to drop the less important variables. Upon inspection, many variables have the extension _CD to represent code and this is accompanied by another column with the names that these codes map to. Let us drop the code variables and keep the more descriptive name mappings.

We should also consider dropping:

• _ID variables as they are only useful for joining to other datasets which is not in the scope of this EDA.
• _NO variables as document numbers are not useful to us.
• CAL_YR, CAL_QTR, FISCAL_YR, FISCAL_QTR can be removed as all this information can be extracted from ACCIDENT_DT and ACCIDENT_TIME
• TOT_MINE_EXPER,MINE_EXPER and JOB_EXPER all indicate work experience. Let us keep TOT_MINE_EXPER as it seems the most relavent to compare with other variables.
• RETURN_TO_WORK_DT, INVEST_BEGIN_DT and NARRATIVE

drop <- c("MINE_ID", "CONTROLLER_ID", "CONTRACTOR_ID", "OPERATOR_ID", "SUBUNIT_CD", "DEGREE_INJURY_CD", "UG_LOCATION_CD", "UG_MINING_METHOD_CD", "MINING_EQUIP_CD", "CLASSIFICATION_CD", "ACCIDENT_TYPE_CD", "OCCUPATION_CD", "FIPS_STATE_CD", "ACTIVITY_CD", "INJURY_SOURCE_CD", "NATURE_INJURY_CD", "INJ_BODY_PART_CD", "IMMED_NOTIFY_CD", "CLOSED_DOC_NO", "DOCUMENT_NO", "CAL_YR", "CAL_QTR", "FISCAL_YR", "FISCAL_QTR", "RETURN_TO_WORK_DT", "INVEST_BEGIN_DT", "NARRATIVE", "EQUIP_MFR_CD", "EQUIP_MODEL_NO", "MINE_EXPER", "JOB_EXPER", "SCHEDULE_CHARGE", "DAYS_RESTRICT")

df <- df[,!(names(df) %in% drop)]

names(df)

 [1] "CONTROLLER_NAME"  "OPERATOR_NAME"    "SUBUNIT"          "ACCIDENT_DT"      "ACCIDENT_TIME"    "DEGREE_INJURY"   
 [7] "UG_LOCATION"      "UG_MINING_METHOD" "MINING_EQUIP"     "EQUIP_MFR_NAME"   "SHIFT_BEGIN_TIME" "CLASSIFICATION"  
[13] "ACCIDENT_TYPE"    "NO_INJURIES"      "TOT_EXPER"        "OCCUPATION"       "ACTIVITY"         "INJURY_SOURCE"   
[19] "NATURE_INJURY"    "INJ_BODY_PART"    "DAYS_LOST"        "TRANS_TERM"       "IMMED_NOTIFY"     "COAL_METAL_IND"  

We can see that some the variable names are ambiguous. Let’s rename some variables to something more intuitive.

df <- rename(df, ACCIDENT_DATE=ACCIDENT_DT, UNDER_GROUND_LOC=UG_LOCATION, UNDER_GROUND_MINING_METHOD=UG_MINING_METHOD, ACCIDENT_CLASSIFICATION=CLASSIFICATION, TOT_MINE_EXPER=TOT_EXPER, IMMEDIATE_NOTIFY=IMMED_NOTIFY)

names(df)

 [1] "CONTROLLER_NAME"            "OPERATOR_NAME"              "SUBUNIT"                    "ACCIDENT_DATE"             
 [5] "ACCIDENT_TIME"              "DEGREE_INJURY"              "UNDER_GROUND_LOC"           "UNDER_GROUND_MINING_METHOD"
 [9] "MINING_EQUIP"               "EQUIP_MFR_NAME"             "SHIFT_BEGIN_TIME"           "ACCIDENT_CLASSIFICATION"   
[13] "ACCIDENT_TYPE"              "NO_INJURIES"                "TOT_MINE_EXPER"             "OCCUPATION"                
[17] "ACTIVITY"                   "INJURY_SOURCE"              "NATURE_INJURY"              "INJ_BODY_PART"             
[21] "DAYS_LOST"                  "TRANS_TERM"                 "IMMEDIATE_NOTIFY"           "COAL_METAL_IND"            

Convert dates and times into correct format

We should aim to combined ACCIDENT_DATE and ACCIDENT_TIME into a date/time object. This also applies to SHIFT_BEGIN_TIME.

# CONVERT `ACCIDENT_TIME` TO A CONSISTANT FORM ["HHMM"]
time <- as.character(df$ACCIDENT_TIME)                                      # convert to string
df$ACCIDENT_TIME <- ifelse( nchar(time) == 3, paste0("0", time), time)      # add leading zero if requ.

time <- as.character(df$SHIFT_BEGIN_TIME)                                   # convert to string
df$SHIFT_BEGIN_TIME <- ifelse( nchar(time) == 3, paste0("0", time), time)   # add leading zero if requ.

# CREATE DATE_TIME COLUMN
date_time <- paste(df$ACCIDENT_DATE, df$ACCIDENT_TIME)
df$DATE_TIME <- as.POSIXct(strptime(date_time, '%d/%m/%Y %H%M') )

date_time <- paste(df$ACCIDENT_DATE, df$SHIFT_BEGIN_TIME)
df$SHIFT_BEGIN_DATE_TIME <- as.POSIXct(strptime(date_time, '%d/%m/%Y %H%M') )

# NOW DROP DATE AND TIME
drop <- c("ACCIDENT_TIME", "ACCIDENT_DATE", "SHIFT_BEGIN_TIME")
df <- df[,!(names(df) %in% drop)]

head(df$DATE_TIME)

[1] "2012-03-14 09:45:00 AWST" "2007-01-08 11:05:00 AWDT" "2009-07-04 10:00:00 AWST" "2000-05-26 11:00:00 AWST"
[5] "2005-01-22 14:30:00 AWST" "2006-03-29 11:30:00 AWST"

head(df$SHIFT_BEGIN_DATE_TIME)

[1] "2012-03-14 06:00:00 AWST" "2007-01-08 07:00:00 AWDT" "2009-07-04 06:00:00 AWST" "2000-05-26 07:00:00 AWST"
[5] "2005-01-22 07:00:00 AWST" "2006-03-29 07:00:00 AWST"

Discretizing continuous variables

TOT_MINE_EXPER is a continuous variable defined as both the years and months of experience as a decimal. Let us investigate the summary statistics.

It makes sense to discretize this data as their exact value matters less than whether they fall into a certain time range. The interquartile range is between \(2\) and \(20\), thus, it seems that a bin size of \(5\) is reasonable. We will replace TOT_MINE_EXPER with its discretized counterpart instead of creating a new variable in order to keep the dataset as simple as possible.

brks <- c(0, 5, 10, 15, 20, Inf)

df$TOT_MINE_EXPER <- cut(df$TOT_MINE_EXPER, breaks=brks, include.lowest = T)
summary(df$TOT_MINE_EXPER)

   [0,5]   (5,10]  (10,15]  (15,20] (20,Inf]     NA's 
   71495    26267    16170    12801    38681    37400 

We should also consider categorising NO_INJURIES as it seems discrete.

table(df$NO_INJURIES)

     0      1      2      3      4      5      6      7      8      9     10     12     13     14     16     36 
 24227 177002   1016    193     97     38     61     35      8     36     21      1     13     14     16     36 

Coerce NO_INJURIES to factor.

df$NO_INJURIES <- factor(df$NO_INJURIES)

Dealing with missing values

Let us now consider where our NAs are.

colSums(is.na(df))

           CONTROLLER_NAME              OPERATOR_NAME                    SUBUNIT 
                       695                        695                          0 
             DEGREE_INJURY           UNDER_GROUND_LOC UNDER_GROUND_MINING_METHOD 
                       910                     120473                     132294 
              MINING_EQUIP             EQUIP_MFR_NAME    ACCIDENT_CLASSIFICATION 
                    111525                     111579                       2108 
             ACCIDENT_TYPE                NO_INJURIES             TOT_MINE_EXPER 
                      2112                          0                      37400 
                OCCUPATION                   ACTIVITY              INJURY_SOURCE 
                     26021                      26334                      26392 
             NATURE_INJURY              INJ_BODY_PART                  DAYS_LOST 
                     26619                      26620                      39677 
                TRANS_TERM           IMMEDIATE_NOTIFY             COAL_METAL_IND 
                     29743                     117715                          0 
                 DATE_TIME      SHIFT_BEGIN_DATE_TIME 
                     18968                      10786 

num.remote.na <- dim(df %>% filter(is.na(CONTROLLER_NAME)))[1]

all.four.na <- df %>% 
    filter(is.na(CONTROLLER_NAME), is.na(OPERATOR_NAME))

paste0("Correlation of missing values: ", round((dim(all.four.na)[1] / num.remote.na) * 100, 2), "%")

[1] "Correlation of missing values: 100%"